package com.iis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import com.iis.databaseutil.DatabaseConnection;
import com.iis.entity.Agent;
import com.iis.entity.Person;

public class AgentDAO {
	private static final AgentDAO agentDAO = new AgentDAO();
private AgentDAO(){
		
	}
	
	public static AgentDAO getInstances(){
		return agentDAO;
	}
	
	Connection koneksiKu = null;

	PreparedStatement psInsertAgent = null;

	String sqlInsertAgent = "INSERT INTO Agent(Agent_No, Region, Person_idPerson) "
			+ "VALUES (?,?,?) ";

	public Agent save(Agent newAgent) throws SQLException {
		if (newAgent == null) {
			return null;
		}
		koneksiKu = DatabaseConnection.getDataConnection(false);
		int index = 1;
		try {
			
			Person newPerson = newAgent;
			
			PersonDAO myPersonDAO = PersonDAO.getInstance();
			
			newPerson = myPersonDAO.save(newPerson);

			psInsertAgent = koneksiKu.prepareStatement(sqlInsertAgent,
					Statement.RETURN_GENERATED_KEYS);
			
			//generate agent no
			Calendar myCal = Calendar.getInstance();
			myCal.setTime(new java.util.Date());
			String newAgentNo = "Agent" + "-" + myCal.get(Calendar.YEAR) + "-" + myCal.get(Calendar.MONTH) + "-" + newPerson.getPersonID();
			newAgent.setAgentNo(newAgentNo);
			
			psInsertAgent.setString(index++, newAgent.getAgentNo());
			psInsertAgent.setString(index++, newAgent.getRegion());
			psInsertAgent.setLong(index++,	 newAgent.getPersonID());
			int jumlahRow = psInsertAgent.executeUpdate();
			if (jumlahRow == 0) {
				throw new SQLException("Gagal menyimpan person");
			}
			try (ResultSet generatedKeys = psInsertAgent.getGeneratedKeys()) {
				if (generatedKeys.next()) {
					newAgent.setAgentID(generatedKeys.getLong(1));
					koneksiKu.commit();
					return newAgent;
				} else {
					throw new SQLException("Gagal menyimpan person");
				}
			} catch (Exception e) {
				koneksiKu.rollback();
			}
			

		} catch (SQLException e) {
			e.printStackTrace();
			koneksiKu.rollback();
		}
		
		return null;
	}
	
	PreparedStatement psSelectAllAgent = null;

	String sqlGetAllAgent = "Select * from agent s, person p where s.person_idPerson = p.idPerson and s.deleted = 'N' and p.deleted = 'N'";
	
	public ArrayList<Agent> getAll(){
		koneksiKu = DatabaseConnection.getDataConnection();
		int index = 1;
		ArrayList<Agent> arrAgent = new ArrayList<Agent>();
		try {

			psSelectAllAgent = koneksiKu.prepareStatement(sqlGetAllAgent);
			ResultSet rs = psSelectAllAgent.executeQuery();

			while (rs.next()) {
				long idAgent = rs.getLong("idAgent");
				String region = rs.getString("Region");
				long idPerson = rs.getLong("idPerson");
				String name = rs.getString("Name");
				String address = rs.getString("Address");
				
				Agent agent = new Agent();
				agent.setAgentID(idAgent);
				agent.setRegion(region);
				agent.setPersonID(idPerson);
				agent.setName(name);
				agent.setAddress(address);

				arrAgent.add(agent);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return arrAgent;
	}
	
	PreparedStatement psSelectAgent = null;

	String sqlGetAgent = "Select * from agent s, person p where s.person_idPerson = p.idPerson and s.deleted = 'N' and p.deleted = 'N' and s.idAgent = ?";
	
	public Agent getAgent(long parIdAgent){
		koneksiKu = DatabaseConnection.getDataConnection(false);
		int index = 1;
		
		try {

			psSelectAgent = koneksiKu.prepareStatement(sqlGetAgent);
			psSelectAgent.setLong(index++, parIdAgent);
			ResultSet rs = psSelectAgent.executeQuery();

			while (rs.next()) {
				long idAgent = rs.getLong("idAgent");
				String region = rs.getString("Region");
				long idPerson = rs.getLong("idPerson");
				String name = rs.getString("Name");
				String address = rs.getString("Address");
				
				Agent agent = new Agent();
				agent.setAgentID(idAgent);
				agent.setRegion(region);
				agent.setPersonID(idPerson);
				agent.setName(name);
				agent.setAddress(address);
				
				return agent;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}
	
	PreparedStatement psUpdateDeleteAgent = null;

	String sqlUpdateDeleteAgent = "update agent a set deleted = 'Y' where a.idAgent = ?";
	
	public boolean deleteAgent(long parIdAgent){
		koneksiKu = DatabaseConnection.getDataConnection();
		int index = 1;
		
		try {

			psUpdateDeleteAgent = koneksiKu.prepareStatement(sqlUpdateDeleteAgent);
			psUpdateDeleteAgent.setLong(index++, parIdAgent);
			psUpdateDeleteAgent.executeUpdate();

			return true;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}
	
	PreparedStatement psUpdateAgent = null;

	String sqlUpdateAgent = "update agent a set region = ? where a.idAgent = ?";
	
	PreparedStatement psUpdatePerson = null;

	String sqlUpdatePersonAgent = "update person p set name = ? where p.idPerson = ?";
	
	public boolean update(Agent agent){
		
		koneksiKu = DatabaseConnection.getDataConnection(false);
		
		int index = 1;
		int index2 = 1;
		
		try {

			psUpdateDeleteAgent = koneksiKu.prepareStatement(sqlUpdateAgent);
			psUpdateDeleteAgent.setString(index++, agent.getRegion());
			psUpdateDeleteAgent.setLong(index++, agent.getAgentID());
			psUpdateDeleteAgent.executeUpdate();
			
			Agent oriAgent = getAgent(agent.getAgentID());
			
			psUpdatePerson = koneksiKu.prepareStatement(sqlUpdatePersonAgent);
			psUpdatePerson.setString(index2++, agent.getName());
			psUpdatePerson.setLong(index2++, oriAgent.getPersonID());
			psUpdatePerson.executeUpdate();
			
			koneksiKu.commit();
			
			return true;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}
}
